<?php
require_once ('library/Model/BaseModel.php');

/**
 *
 * @author Boo
 *        
 */
class Trip extends BaseModel implements IModel{
	public function getId(){
		return array('id','car','driver','route');
	}
	/**
	 * hien thi cac list Trip chua chay
	 * starttime > ngay hien tai
	 */
	public function waitTrip(){
		$now= iDate::getNowdate();
		$sql = "select * from trip where starttime > '".$now."'";
		$result = $this->db->fetchAll ( $sql );
		return $result;
	} 
	/**
	 * 
	 * @param int $id
	 * @return quantity
	 */
	public function getquantitybyid($id){
		$sql = 'select quantity from trip where id = '.$id;
		return $this->db->fetchRow($sql);
	}
	/**
	 * ++1 quantity
	 * @param unknown $id
	 * @return number of row inserted or updated. 
	 */
	public function upQuantity($id){
		$entity = $this->getquantitybyid($id); 
 		$q =  $entity['quantity'] + 1;
		$sql = 'update trip set quantity = '.$q.' where id = '.$id;
		return $this->db->execute ( $sql );
	}
	
	
	/* (non-PHPdoc)
	 * @see BaseModel::getall()
	 */
	public function getall($strlimit = NULL) {
		// TODO Auto-generated method stub
		$sql =	'select t.code,p1.name as pointstart,p2.name as pointend,t.id,c.numberplate as car,d.username as driver,t.starttime,t.endtime,p1.name as pointstart,p2.name as pointend,t.updatetime,t.latitude,t.longtitude,t.saleoff,t.quantity as statusquantity,t.status
				from trip as t
				left join car as c on c.id= t.car
				left join driver as d on d.id = t.driver
				left join point as p1 on t.route = p1.route and p1.position in (select min(p1.position) from point as p1 where  p1.route = t.route)
				left join point as p2 on t.route = p2.route and p2.position in (select max(p2.position) from point as p2 where  p2.route = t.route)
				order by t.id ASC '.$strlimit;
		return $this->db->fetchAll ( $sql );
	}

	/**
	 * --1 quantity
	 * @param unknown $id
	 * @return number of row inserted or updated.
	 */
	public function downQuantity($id){
		$entity = $this->getquantitybyid($id);
		$q =  $entity['quantity'] - 1;
		$sql = 'update trip set quantity = '.$q.' where id = '.$id;
		return $this->db->execute ( $sql );
	}
	/**
	 * 
	 */
	public function gettripandselloff($strlimit = NULL){
		$curdate = iDate::getNowdate();
		$sql = "select 	t.id as tripid,
						r.id as routeid,
						p1.name as pointstart,
						p2.name as pointend,
						t.starttime as starttime,
						t.saleoff as saleoff,
						r.price as price
				from trip as t
				left join route as r on t.route = r.id
					left join point as p1 on p1.route = r.id and p1.position in (select min(p1.position) from point as p1 where  p1.route = r.id)
						left join point as p2 on p2.route = r.id and p2.position in (select max(p2.position) from point as p2 where  p2.route = r.id ) 
				where t.status = 1 and t.starttime > '".$curdate."' order by starttime ASC ".$strlimit;
		return $this->db->fetchAll($sql);
	}
	/* (non-PHPdoc)
	 * @see BaseModel::insert()
	 */
	public function insert(array $entity) {
		// TODO Auto-generated method stub
		$entity['code'] = $this->generateCode($entity['route']);
		if (iDate::comparenow($entity['starttime']) && iDate::compare($entity["starttime"],$entity["endtime"])){
			iDebug::debugArray($entity);
			$sql = "insert into trip (code,car,driver,starttime,endtime,route,updatetime,latitude,longtitude,saleoff,quantity,status) value 
					('".$entity['code']."',".$entity['car'].",".$entity['driver'].",'".$entity['starttime']."','".$entity['endtime']."',".$entity['route'].",0,".$entity['latitude'].",".$entity['longtitude'].",".$entity['saleoff'].",1,1)";
			if ($this->db->execute($sql)>0) {
				$x = $this->getByField($entity['car'], $entity['driver'], $entity['starttime']);
				$sql1 = 'select c.quantity as carquantity
						from trip as t left join car as c on t.car = c.id where t.id = '.$x['id'];
				$en = $this->db->fetchRow($sql1);
				$s = new Seat();
				$s->generateSeatByTrip($x['id'], $en['carquantity']);
				return true;
			}	
		}
		return false;
	}
	
	/* (non-PHPdoc)
	 * @see BaseModel::update()
	 */
	public function update(array $entity) {
		// TODO Auto-generated method stub
		$id = $entity ['id'] ;
		iDebug::debugArray($id);
		if (iDate::comparenow($entity['starttime']) && iDate::compare($entity["starttime"],$entity["endtime"])){
			$old = $this->getbyid(array('id'=>$entity ['id']));
			if ($entity['route'] != $old['route'])
				$entity['code'] = $this->generateCode($entity['route']);
			if ($entity['car'] != $old['car']){ 
				$t = new Trip();
				$t->deleteListSeatByTrip($id);
				$xsql = 'select quantity from car where id ='.$entity['car'];
				$en = $this->db->fetchRow($xsql);
				$s = new Seat();
				$s->generateSeatByTrip($id, $en['quantity']);
			}
			iDebug::debugArray($entity);
			$entity = iArray::removeAtTop ( $entity, 1 );
			$b = sizeof ( $entity );
			$c = 0;
			$sql = "update " . $this->getTable () . " set ";
			foreach ( $entity as $key => $value ) {
				$c += 1;
				if ($b == $c)
					$sql .= $key . " = " . "'" . $value . "'";
				else
					$sql .= $key . " = " . "'" . $value . "'" . ",";
			}
			$sql .= " where id = " . $id;
			echo $sql;
			return $this->db->execute ( $sql );
		}
	}

	public function getByField($car,$driver,$starttime){
		$sql = "select id from trip where car = ".$car." and driver = ".$driver." and starttime = '".$starttime."'";
		return $this->db->fetchRow($sql);
	}

	/**
	 *  update sale off cac trip chua chay
	 *  array
	 */
	public function updateSaleoff($id,$saleoff){
		$sql= "update trip set saleoff ='".$saleoff."' where id =".$id;
		$this->db->execute($sql);
	}
	/* (non-PHPdoc)
	 * @see BaseModel::generateCode()
	 */
	public function generateCode($key) {
		// TODO Auto-generated method stub
		$sql = 'select code from route where id = '.$key;
		$code = $this->db->fetchRow($sql);
		$code = $code['code'].'T';
		$xsql = 'select code from trip where code like "'.$code.'%" order by code DESC limit 0,1';
		$x = $this->db->fetchRow($xsql);
		$x = intval(substr($x['code'], 7,strlen($x['code']))) + 1;
		if ($x < 10)
			return $code = $code.'0'.$x;
		return $code = $code.$x;
	}
	
	/**
	 * 
	 */
	public function getTripPrepare(){
		$sql = "select c.numberplate,p1.name as pointstart , p2.name as pointend, t.starttime
				from trip as t
				left join car as c on t.car = c.id
				left join point as p1 on p1.route = t.route and p1.position in (select min(p1.position) from point as p1 where  p1.route = t.route)
				left join point as p2 on p2.route = t.route and p2.position in (select max(p2.position) from point as p2 where  p2.route = t.route )
				where starttime >'".iDate::getNowdate()."'order by starttime ASC ";
		return $this->db->fetchAll($sql);
		//return $sql;
	}
	/**
	 *
	 */
	public function getListTripPrepare(){
		$sql =	'select t.code, CONCAT_WS(" - ",p1.name,p2.name) as routename,t.id,c.numberplate as car,d.username as driver,t.starttime,t.endtime,t.updatetime,t.latitude,t.longtitude,(((t.saleoff/100)*r.price)+r.price) as price
				from trip as t
				left join car as c on c.id= t.car
				left join driver as d on d.id = t.driver
				left join route as r on r.id = t.route
				left join point as p1 on t.route = p1.route and p1.position in (select min(p1.position) from point as p1 where  p1.route = t.route)
				left join point as p2 on t.route = p2.route and p2.position in (select max(p2.position) from point as p2 where  p2.route = t.route)
				where t.status = 1
				order by t.id ASC';
		return $this->db->fetchAll ( $sql );
	}
}
?>